package com.ets.Expense_DAO;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/******************************************************************************* 
 The <big><b>ExpenseDB</b></big> class implements the reader & writer methods of
 the <b>ExpenseDAO</b> interface to provide the data access to the
 <big><b>Expense</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
*******************************************************************************/ 

public class ExpenseDB extends DB_Pipe implements ExpenseDAO
  {
   public ExpenseDB()
	 {
	 }

   /*******************************************************************************
	Expense reader methods
   *******************************************************************************/
   
   public boolean existsExpense (int stckNo, int seq)
     {
      String exists = "SELECT Seq "         +
                        "FROM expense "     +
                       "WHERE Stck_No = ? " +
                         "AND Seq     = ?";
	  try
		{
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setInt(1, stckNo);
		 ps.setInt(2, seq);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public Expense getExpense (int stckNo, int seq)
     {
	  String sel = "SELECT Stck_No, "    +
	                      "Seq, "        +
	                      "Dt, "         +
	                      "Typ, "        +
	                      "Vndr, "       +
	                      "Desc, "       +
	                      "Qty, "        +
	                      "Price, "      +
	                      "Tax "         +
	                 "FROM expense "     +
	                "WHERE Stck_No = ? " +
	                  "AND Seq     = ?";
      try
		{
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setInt(1, stckNo);
		 ps.setInt(2, seq);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    Date   Dt    = rs.getDate("Dt");
		    String Typ   = rs.getString("Typ");
		    String Vndr  = rs.getString("Vndr");
		    String Desc  = rs.getString("Desc");
		    double Qty   = rs.getDouble("Qty");
		    double Price = rs.getDouble("Price");
		    double Tax   = rs.getDouble("Tax");
		    Expense e    = new Expense(stckNo, seq, Dt, Typ, Vndr, Desc, Qty, Price, Tax);
		    rs.close();
		    ps.close();
		    return e;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean getExpenses (ArrayList<Expense> expns, int stckNo)
     {
      String qry = "SELECT Stck_No, "       +
                          "Seq, "           +
                          "Dt, "            +
                          "Typ, "           +
                          "Vndr, "          +
                          "Desc, "          +
                          "Qty, "           +
                          "Price, "         +
                          "Tax "            +
                     "FROM expense "        +
                    "WHERE Stck_No = ? "    +
                    "ORDER BY Stck_No ASC," +
                             "Seq     ASC";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(qry);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    int     Stck_No = rs.getInt("Stck_No");
		    int     Seq     = rs.getInt("Seq");
		    Date    Dt      = rs.getDate("Dt");
		    String  Typ     = rs.getString("Typ");
		    String  Vndr    = rs.getString("Vndr");
		    String  Desc    = rs.getString("Desc");
		    double  Qty     = rs.getDouble("Qty");
		    double  Price   = rs.getDouble("Price");
		    double  Tax     = rs.getDouble("Tax");
		    Expense e       = new Expense(Stck_No, Seq, Dt, Typ, Vndr, Desc, Qty, Price, Tax);
		    expns.add(e);
		   }
		 rs.close();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   /*******************************************************************************
	Expense writer methods
   *******************************************************************************/
   
   /*******************************************************************************
    When adding a new <big><b>Expense</b></big> row to the database, the calling
    class <big><b>DOES NOT</b></big> provide the sequence number (Seq) value. This
    method is responsible for calculating the next expense sequence number for
    this vehicle.
   *******************************************************************************/
   public boolean addExpense (Expense e)
     {
	  String ins = "INSERT "                     +
                     "INTO expense (Stck_No, "   +
                                   "Seq, "       +
                                   "Dt, "        +
                                   "Typ, "       +
                                   "Vndr, "      +
                                   "Desc, "      +
                                   "Qty, "       +
                                   "Price, "     +
                                   "Tax, "       +
                                   "TMS_Crtd, "  +
                                   "User_Crtd, " +
                                   "TMS_Updt, "  +
                                   "User_Updt) " +
	               "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setInt(1, e.getStckNo());
		 ps.setInt(2, e.getSeq());
		 Date tmpDt = (Date) e.getDt();
		 ps.setDate(3, tmpDt);
		 ps.setString(4, e.getTyp());
		 ps.setString(5, e.getVndr());
		 ps.setString(6, e.getDesc());
		 ps.setDouble(7, e.getQty());
		 ps.setDouble(8, e.getPrice());
		 ps.setDouble(9, e.getTax());
		 ps.setTimestamp(10, ts);
		 ps.setString(11, "USER0001");
		 ps.setTimestamp(12, ts);
		 ps.setString(13, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
			}
		  catch(SQLException sqle)
			{
			 //sqle.printStackTrace();	// for debugging
			 return false;
			}
		  finally
			{
			 DB_Disconnect(DB_Pipe);
			}
     }
   
   public boolean updateExpense (Expense e)
     {
	  String upd = "UPDATE expense "       +
                      "SET Dt = ?, "       +
                          "Typ = ?, "      +
                          "Vndr = ?, "     +
                          "Desc = ?, "     +
                          "Qty = ?, "      +
                          "Price = ?, "    +
                          "Tax = ?, "      +
                          "TMS_Updt = ?, " +
                          "User_Updt = ? " +
                    "WHERE Stck_No = ? "   +
                      "AND Seq     = ?";
		  try
			{
	         DB_Pipe              = DB_Connect();
			 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
		     Timestamp         ts = DateUtils.getTmstmp();
			 Date tmpDt = (Date) e.getDt();
			 ps.setDate(1, tmpDt);
			 ps.setString(2, e.getTyp());
			 ps.setString(3, e.getVndr());
			 ps.setString(4, e.getDesc());
			 ps.setDouble(5, e.getQty());
			 ps.setDouble(6, e.getPrice());
			 ps.setDouble(7, e.getTax());
			 ps.setTimestamp(8, ts);
			 ps.setString(9, "USER8888");
			 ps.setInt(10, e.getStckNo());
			 ps.setInt(11, e.getSeq());
			 ps.executeUpdate();
			 ps.close();
			 return true;
			}
		  catch(SQLException sqle)
			{
			 //sqle.printStackTrace();	// for debugging
			 return false;
			}
		  finally
			{
			 DB_Disconnect(DB_Pipe);
			}
     }
   
   public boolean deleteExpense (Expense e)
     {
	  String del = "DELETE "             +
                     "FROM expense "     +
	                "WHERE Stck_No = ? " +
	                  "AND Seq     = ?";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
		 ps.setInt(1, e.getStckNo());
		 ps.setInt(2, e.getSeq());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
  }
